from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from tkinter import font as tkfont
from threading import Thread
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
from sql import sqlseverDB
from config import *
import tkinter.messagebox as messagebox
import openpyxl,time,concurrent.futures

class Data2Fra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        DCMainFrame(self).pack(fill=X,padx=3,pady=3)

class DCMainFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.kwcountStr=StringVar()
        self.kwcountStr.set('正在获取shopee-id产品数量...')
        
        self.createWidgets()

    def createWidgets(self):
        
        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=2)
        Label(self, text='导出模板信息表', fg="black", bg="yellow",font=("微软雅黑", 13)).pack(fill=X,padx=5)
        self.cmb=mbDiv(self)
        Thread(target=self.getkwcount).start()
        f0=Frame(self)
        f0.pack(fill=X)

        lrf=Frame(f0)
        lrf.pack(side=LEFT,fill=Y,padx=2,pady=2)

        f00=Frame(lrf)
        f00.pack(fill=X,padx=2,pady=2)
        Label(f00, textvariable=self.kwcountStr,fg='red').pack(side=LEFT, padx=2, pady=2)
        label_font = tkfont.Font(family="微软雅黑", size=10, weight="bold")
        f01=Frame(lrf)
        f01.pack(fill=X,padx=2,pady=2)
        Label(f01, text='评分:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.PfIpt = Entry(f01,width=5)
        self.PfIpt.pack(side=LEFT, padx=2, pady=2)
        self.PfIpt.insert(0, '0')
        Label(f01, text='评价数量:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.CmtIpt = Entry(f01,width=5)
        self.CmtIpt.pack(side=LEFT, padx=2, pady=2)
        self.CmtIpt.insert(0, '0')
        Label(f01, text='月销量:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.SkuSoldIpt = Entry(f01,width=5)
        self.SkuSoldIpt.pack(side=LEFT, padx=2, pady=2)
        self.SkuSoldIpt.insert(0, '1')
        Label(f01, text='总销量:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.TotalSoldIpt = Entry(f01,width=5)
        self.TotalSoldIpt.pack(side=LEFT, padx=2, pady=2)
        self.TotalSoldIpt.insert(0, '1')

        f02=Frame(lrf)
        f02.pack(fill=X,padx=2,pady=2)
        Label(f02, text='汇率参数:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.HlIpt = Entry(f02,width=5)
        self.HlIpt.pack(side=LEFT, padx=2, pady=2)
        self.HlIpt.insert(0, '280')
        Label(f02, text='价格区间:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.MinPriceIpt = Entry(f02,width=6)
        self.MinPriceIpt.pack(side=LEFT, padx=2, pady=2)
        self.MinPriceIpt.insert(0, '800')
        Label(f02,text='~').pack(side=LEFT, padx=2, pady=2)
        self.MaxPriceIpt = Entry(f02,width=6)
        self.MaxPriceIpt.pack(side=LEFT, padx=2, pady=2)
        self.MaxPriceIpt.insert(0, '6000')
        Label(f02, text='价格倍数:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.PbsIpt = Entry(f02,width=6)
        self.PbsIpt.pack(side=LEFT, padx=2, pady=2)
        self.PbsIpt.insert(0, '1')

        f03=Frame(lrf)
        f03.pack(fill=X,padx=2,pady=2)
        Label(f03, text='产品数:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.EveIpt = Entry(f03,width=6)
        self.EveIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f03, text='表份数:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.FileCountIpt = Entry(f03,width=6)
        self.FileCountIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f03, text='模版数:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.MbCountIpt = Entry(f03,width=6)
        self.MbCountIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f03, text='授权码:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.SqmIpt = Entry(f03,width=6)
        self.SqmIpt.pack(side=LEFT, padx=2, pady=2)
        self.SqmIpt.insert(0, 'cai5415')

        f04=Frame(lrf)
        f04.pack(fill=X,padx=2,pady=2)
        self.dcbtn=Button(f04,text='导出(印尼标题)',command=lambda:Thread(target=self.start_dc).start(),fg='#ffffff',bg='green',width=20)
        self.dcbtn.pack(side=LEFT, padx=2, pady=2)
        
        rf=Frame(f0)
        rf.pack(side=RIGHT,fill=Y)
        rollDiv(rf)

    
    def getkwcount(self):
        while True:
            try:
                mindc=self.cmb.mindc if self.cmb.mindc is not None else 0
                rs=self.ms.ExecQuery('''SELECT COUNT(DISTINCT itemid) from ProShopeePh where rating_star>=0 and skuSold>=1 
                                        and historical_sold>=1 and cmt_count>=0 and (skuPirce between 800 and 6000) and ExportConditionMet=1 and daochu<99''')
                rs1=self.ms.ExecQuery(f'''SELECT COUNT(DISTINCT itemid) from ProShopeePh where daochu={mindc} and rating_star>=0 and skuSold>=1 
                                        and historical_sold>=1 and cmt_count>=0 and (skuPirce between 800 and 6000) and ExportConditionMet=1''')

                self.kwcountStr.set(f'共有产品数据 {rs[0][0]} 个,其中 {rs1[0][0]} 个产品未导出')
                time.sleep(10)

            except Exception as e:
                print(e)
    
    def start_dc(self):

        if not self.EveIpt.get().strip() or not self.FileCountIpt.get().strip() or not self.MbCountIpt.get().strip():
            messagebox.showinfo('提示','请输入完整导出信息')
            return

        sqm=self.SqmIpt.get().strip()
        if sqm!='cai5415':
            messagebox.showinfo('提示','请导入正确的授权码')
            return
        self.dcbtn.config(state=DISABLED)  
        
        minp=float(self.MinPriceIpt.get().strip())
        maxp=float(self.MaxPriceIpt.get().strip())
        pf=float(self.PfIpt.get().strip())
        yxl=int(self.SkuSoldIpt.get().strip())
        zxl=int(self.TotalSoldIpt.get().strip())
        cmt_count=int(self.CmtIpt.get().strip())
        everycount=int(self.EveIpt.get().strip())
        filecount=int(self.FileCountIpt.get().strip())
        phl=float(self.HlIpt.get().strip())
        pbs=float(self.PbsIpt.get().strip())
        mbcount=int(self.MbCountIpt.get().strip())

        print(f'筛选本次可导出的模版ID...')

        sqlstr='''
            select min(daochu) from ProShopeePh 
            where rating_star>=?
            and skuSold>=?
            and historical_sold>=?
            and cmt_count>=?
            and (skuPirce between ? and ?) 
            and ExportConditionMet=1
        '''
        mindc=trydosql(self.ms,sqlstr,(pf,yxl,zxl,cmt_count,minp,maxp))[0][0]
        print(f'获取过滤条件以后的最小导出值为:{mindc}')
        sqlstr2=f'''
                select top {mbcount} mb1.did,
                (SELECT COUNT(DISTINCT itemid) from ProShopeePh 
                where pmbid=mb1.did 
                and daochu=?
                and rating_star>=?
                and skuSold>=?
                and historical_sold>=?
                and cmt_count>=?
                and (skuPirce between ? and ?)
                and ExportConditionMet=1
                ) as yndccount
                from mb as mb1 where duid=4 and dstate=2 order by yndccount desc
            '''
        rs=trydosql(self.ms,sqlstr2,(mindc,pf,yxl,zxl,cmt_count,minp,maxp))
        print(f'共获取{len(rs)} 个符合本次导出的MBid')

        current_time_str = time.strftime("%Y-%m-%d_%H-%M-%S", time.localtime(time.time()))
        dcmr=f'导出结果{current_time_str}'
        os.mkdir(dcmr)
        for mbid,yndccount in rs:
            pros=getsmtpro([mbid,pf,yxl,zxl,cmt_count,minp,maxp,mindc],everycount,filecount)
            print(f'模板id:{mbid},成功获取产品：{len(pros)}')
            dc_exceldata(pros,mbid,everycount,filecount,phl,pbs,dcmr)
            print(f'模板id:{mbid},成功导出(印尼标题)')
            print()
        print(f'全部导出完毕(印尼标题)')
        messagebox.showinfo('提示',f'全部导出完毕(印尼标题)')


        self.dcbtn.config(state=NORMAL)

class rollDiv(Frame):

    def __init__(self,master=None):
        Frame.__init__(self, master)
        self.pack(fill=X,pady=2,padx=2)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.statustxt=StringVar()
        self.statustxt.set('未选择路径')
        self.createWidgets()
    def createWidgets(self):

        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        
        f00=Frame(f0)
        f00.pack(pady=5,fill=X,padx=5)
        Label(f00, text='回溯路径:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f00)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f00,text='选择回溯路径',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)


        f01=Frame(f0)
        f01.pack(fill=X,pady=2,padx=2)
        Label(f01, textvariable=self.statustxt,fg='red').pack(side=LEFT, padx=2, pady=2)

        f02=Frame(f0)
        f02.pack(fill=X,pady=2,padx=2)
        self.bkbtn=Button(f02,text='回溯目录下所有一次铺货产品',fg='#ffffff',bg='green',command=lambda:Thread(target=self.back_data).start())
        self.bkbtn.pack(side=LEFT,padx=2, pady=2)

        self.bk2btn=Button(f02,text='回溯目录下所有二次铺货产品',fg='#ffffff',bg='green',command=lambda:Thread(target=self.back2_data).start())
        self.bk2btn.pack(side=LEFT,padx=2, pady=2)

        f03=Frame(f0)
        f03.pack(fill=X,pady=2,padx=2)

        self.bk3btn=Button(f03,text='回溯目录下所有入仓链接产品',fg='#ffffff',bg='green',command=lambda:Thread(target=self.back3_data).start())
        self.bk3btn.pack(side=LEFT,padx=2, pady=2)

    
    def choosefile(self):

        select_path=filedialog.askdirectory()
        self.DaoRuIpt.delete(0, END)
        self.DaoRuIpt.insert(0,select_path)
        self.fpath=select_path
        cfs=os.listdir(select_path)
        self.itemids=[]
        for fname in cfs:
            cfpath=f'{select_path}/{fname}'
            wb=openpyxl.load_workbook(cfpath)
            ws=wb['Template']
            id_col=4
            for col in range(1,ws.max_column+1):
                if ws.cell(row=3,column=col).value.lower()=='parent sku':
                    id_col=col
                    break
            
            for row in range(7,ws.max_row+1):
                cur_itemid=ws.cell(row=row,column=id_col).value
                if cur_itemid and (cur_itemid not in self.itemids):
                    self.itemids.append(cur_itemid)
        self.statustxt.set(f'当前目录下共有 {len(self.itemids)} 个产品')
        
    def back_data(self):
        itemids=self.itemids
        if not self.DaoRuIpt.get().strip() or len(itemids)==0:
            messagebox.showinfo('提示','请先选择目录')
            return
    
        askys=messagebox.askyesno('提示',f'确定回溯 {len(itemids)} 个产品的状态值')
        if not askys:
            return
        self.bkbtn.config(state=DISABLED)

        itemidsStr=','.join([f"'{itemid}'" for itemid in itemids])
        sqlStr=f'update ProShopeePh set daochu=0 where daochu!=0 and itemid in ({itemidsStr})'
        aff=self.ms.ExecNoQuery(sqlStr)

        print('回溯完毕')
        messagebox.showinfo('提示','回溯完毕')

        self.bkbtn.config(state=NORMAL)

    def back2_data(self):
        itemids=self.itemids
        if not self.DaoRuIpt.get().strip() or len(itemids)==0:
            messagebox.showinfo('提示','请先选择目录')
            return
    
        askys=messagebox.askyesno('提示',f'确定回溯 {len(itemids)} 个产品二次铺货的状态值')
        if not askys:
            return
        self.bk2btn.config(state=DISABLED)

        itemidsStr=','.join([f"'{itemid}'" for itemid in itemids])
        sqlStr=f'update ProShopeePh set daochu2=0 where itemid in ({itemidsStr})'
        aff=self.ms.ExecNoQuery(sqlStr)

        print(f'回溯完毕,共江{aff} 个sku状态置0')
        messagebox.showinfo('提示','二次铺货回溯完毕')

        self.bk2btn.config(state=NORMAL)
    
    def back3_data(self):
        itemids=self.itemids
        if not self.DaoRuIpt.get().strip() or len(itemids)==0:
            messagebox.showinfo('提示','请先选择目录')
            return
    
        askys=messagebox.askyesno('提示',f'确定回溯 {len(itemids)} 个产品入仓的状态值')
        if not askys:
            return
        self.bk3btn.config(state=DISABLED)

        itemidsStr=','.join([f"'{itemid}'" for itemid in itemids])
        sqlStr=f'update ProShopeePh set daochu3=0,isCjRcUrl=0 where itemid in ({itemidsStr})'
        aff=self.ms.ExecNoQuery(sqlStr)

        print(f'回溯完毕,共江{aff} 个sku状态置0')
        messagebox.showinfo('提示','入仓产品回溯完毕')

        self.bk3btn.config(state=NORMAL)

class mbDiv(Frame):
    def __init__(self,master=None):
        Frame.__init__(self, master)
        self.ms=sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.pack(fill=X,pady=2,padx=2)
        self.createWidgets()

    def createWidgets(self):
        
        self.mbStr=StringVar()
        self.mbStr.set('正在获取模板数据...')
        f2=Frame(self)
        f2.pack(fill=X,padx=2,pady=2)
        Label(f2, textvariable=self.mbStr,fg='red').pack(side=LEFT, padx=2, pady=2)
        self.mindc=None
        Thread(target=self.getmbcount).start()


        self.firstjz=True
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        columns = ("did","dpcount","ynpcount","ynwdpcount","curdc")
        tree = ttk.Treeview(f0, show = "headings", columns = columns)
        tree.column("did", anchor = "center",width=150)
        tree.column("dpcount", anchor = "center",width=150)
        tree.column("ynpcount", anchor = "center",width=150)
        tree.column("ynwdpcount", anchor = "center",width=150)
        tree.column("curdc", anchor = "center",width=150)


        tree.heading("did", text = "模板id")
        tree.heading("dpcount", text = "产品总数量")
        tree.heading("ynpcount", text = "印尼总数量")
        tree.heading("ynwdpcount", text = "未导出印尼")
        tree.heading("curdc", text = "daochu最小值")
        
        self.VScroll1 = Scrollbar(f0, orient='vertical', command=tree.yview)
        self.VScroll1.pack(side=RIGHT,fill=Y)
        tree.configure(yscrollcommand=self.VScroll1.set)
        tree.pack(padx=3)
        self.tree=tree
        Thread(target=self.getmbData).start()

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)

        self.btn_catmb=Button(f1,text='刷新',command=lambda:Thread(target=self.upmb).start(),fg='#ffffff',bg='green',width=20)
        self.btn_catmb.pack(side=LEFT, padx=2, pady=2)

        self.dcbtn=Button(f1,text='导出模板信息表',fg='#ffffff',bg='green',command=lambda:Thread(target=self.dcmbxx).start())
        self.dcbtn.pack(side=LEFT,padx=2, pady=2)


    def getmbcount(self):
 
        while True:
            try:
                dcstr='正在获取最小daochu' if self.mindc is None else f'最小daochu为{self.mindc}'
                rs0=self.ms.ExecQuery('select count(did) from mb where duid=4')
                rs1=self.ms.ExecQuery('select count(did) from mb where duid=4 and dstate=2')
                rs2=self.ms.ExecQuery('select count(did) from mb where duid=4 and dstate=2 and dpstatus=1')
                self.mbStr.set(f'共有 {rs0[0][0]} 套模板,{rs1[0][0]} 套模板已经完成,其中有数据模板有 {rs2[0][0]} 套,{dcstr}')
                time.sleep(10)
            except Exception as e:
                print(e)

    def upmb(self):
        self.btn_catmb.config(state=DISABLED)
        self.getmbData()
        self.btn_catmb.config(state=NORMAL)
    
    def getmbData(self):
        if not self.firstjz:
            for _ in map(self.tree.delete, self.tree.get_children("")):
                pass
        else:
            self.firstjz=False
        sqlstr='''
                select min(daochu) from ProShopeePh 
                where rating_star>=0 
                and skuSold>=1 
                and historical_sold>=1
                and cmt_count>=0 
                and (skuPirce between 800 and 6000) 
                and ExportConditionMet=1
                '''
        mindc=trydosql(self.ms,sqlstr)[0][0]
        self.mindc=mindc
        rs=self.ms.ExecQuery(f'''select mb1.did,
                        (SELECT COUNT(DISTINCT itemid) from ProShopeePh where pmbid=mb1.did) as dpcount,
                        (SELECT COUNT(DISTINCT itemid) from ProShopeePh where pmbid=mb1.did and yntitle is not null and rating_star>=0 and skuSold>=1 and historical_sold>=1 and cmt_count>=0 
                        and (skuPirce between 800 and 6000) and ExportConditionMet=1) as yncount,
                        (SELECT COUNT(DISTINCT itemid) from ProShopeePh where pmbid=mb1.did and daochu={mindc} and rating_star>=0 and skuSold>=1 and historical_sold>=1 and cmt_count>=0 
                        and (skuPirce between 800 and 6000) and ExportConditionMet=1) as yndccount,
                        (select min(daochu) from ProShopeePh where pmbid=mb1.did and rating_star>=0 and skuSold>=1 and historical_sold>=1 and cmt_count>=0 
                        and (skuPirce between 800 and 6000) and ExportConditionMet=1) as mbmindc
                        from mb as mb1 where duid=4 and dstate=2 order by yndccount desc''')

        i=1
        for v in rs:
            cv=list(v)
            cvalues=cv
            self.tree.insert('', i, values =cvalues)
            i += 1


    def dcmbxx(self):

        self.dcbtn.config(state=DISABLED)
        rs=self.ms.ExecQuery('''select mb1.did,mb1.dname,mb1.dstate,mb1.duid,mb1.iskw,
                    (SELECT COUNT(DISTINCT itemid) from ProShopeePh pmbid=mb1.did as dpcount
                    from mb as mb1 where duid=4''')
        
        wb=openpyxl.Workbook()
        ws=wb[wb.sheetnames[0]]
        ws.append(['模板id','模板名称','编辑状态','用户id','是否上传关键词','产品数量'])

        for r in rs:
            ws.append(list(r))

        wb.save('模板信息表.xlsx')
        print('全部导出完毕')
        messagebox.showinfo('提示','全部导出完毕')

        self.dcbtn.config(state=NORMAL)


# import tkinter
# list_win=tkinter.Tk()
# list_win.wm_geometry("800x650")
# list_win.title('管理员功能')
# Data2Fra(list_win).pack(fill=X,padx=3,pady=3)
# list_win.mainloop()